package com.sino.second.dao.hibernate;

import java.util.List;

import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.sino.second.bean.TblXmfs3;
import com.sino.second.bean.TblXmjg2;
import com.sino.second.bean.TblXmjg3;
import com.sino.second.dao.IExpertsVoteDAO;

public class ExpertsVoteDAO extends HibernateDaoSupport implements
		IExpertsVoteDAO {
	/**
	 * 查询列出复审结果表中的数据，并按照建议等价进行排序
	 */
	@SuppressWarnings("unchecked")
	public List<TblXmjg2> listAllPastSecondReviewProject(String hqlWhere) {
		String hql = "from TblXmjg2 tx2";
		if (!"".equals(hqlWhere) && hqlWhere.length() > 0) {
			hql = hql + " where " + hqlWhere;
		}
		hql = hql + " order by tx2.sysXmpsdj.id";
		return (List<TblXmjg2>) this.getHibernateTemplate().find(hql);
	}

	/**
	 * 保存数据到大评委分数表
	 */
	public void saveTblXmfs3(TblXmfs3 xmfs3) {
		this.getHibernateTemplate().save(xmfs3);

	}

	/**
	 * 更新大评委分数表
	 */
	public void updateTblXmfs3(TblXmfs3 xmfs3) {
		this.getHibernateTemplate().update(xmfs3);
	}

	/**
	 * 根据项目id，专家id查找大评委分数表
	 */
	public TblXmfs3 findTblXmfs3ByBasicInfoAndExpert(String basicInfoId,
			Integer ExpertId, String hqlWhere) {
		String hql = "from TblXmfs3 tx3 where tx3.basicinfo.zlid=? and tx3.tblZhuanjiarukuxinxibiao.FBh=?";
		if (!"".equals(hqlWhere) && hqlWhere.length() > 0) {
			hql = hql + " and " + hqlWhere;
		}
		return (TblXmfs3) this.getSession().createQuery(hql).setParameter(0,
				basicInfoId).setParameter(1, ExpertId).uniqueResult();
	}

	/**
	 * 根据专家id查找大评委分数表
	 */
	@SuppressWarnings("unchecked")
	public List<TblXmfs3> listTblXmfs3ByExpert(Integer expertId, String hqlWhere) {
		String hql = "from TblXmfs3 tx3 where tx3.tblZhuanjiarukuxinxibiao.FBh=?";
		if (!"".equals(hqlWhere) && hqlWhere.length() > 0) {
			hql = hql + " and " + hqlWhere;
		}
		return (List<TblXmfs3>) this.getHibernateTemplate().find(hql, expertId);
	}

	/**
	 * 对大评委分数表进行计算，得出项目id、项目名称、项目学科、建议等级id、建议等级名称、特等奖票数、一等奖票数、二等奖票数、三等奖票数、弃权票数
	 */
	public List<?> listTblXmfs3VoteResult(String hqlWhere) {
		String hql = "select xmfs3.basicinfo.zlid,xmfs3.basicinfo.name,xmfs3.basicinfo.xk.name,xmfs3.jydj.id,xmfs3.jydj.name,"
				+ "sum(case xmfs3.pxdj.id when '00' then 1 else 0 end) as tedengjaing,"
				+ "sum(case xmfs3.pxdj.id when '01' then 1 else 0 end) as firstPrize,"
				+ "sum(case xmfs3.pxdj.id when '02' then 1 else 0 end) as secondPrize,"
				+ "sum(case xmfs3.pxdj.id when '03' then 1 else 0 end) as thirdPrize,"
				+ "sum(case when xmfs3.pxdj is null then 1 else 0 end) as abstention "
				+ "from TblXmfs3 xmfs3";
		if (!"".equals(hqlWhere) && hqlWhere.length() > 0) {
			hql = hql + " where " + hqlWhere;
		}
		hql = hql
				+ " group by xmfs3.basicinfo.zlid order by xmfs3.basicinfo.xk.id ";
		return this.getSession().createQuery(hql).list();
	}

	/**
	 * 查询大评委分数表，列出已经投票的专家数
	 */
	public int listAlreadyVoteExpertCount(String hqlWhere) {
		String hql = "select count(distinct tx3.tblZhuanjiarukuxinxibiao) from TblXmfs3 tx3 ";
		if (!"".equals(hqlWhere) && hqlWhere.length() > 0) {
			hql = hql + " where " + hqlWhere;
		}
		return (Integer) this.getSession().createQuery(hql).uniqueResult();
	}

	/**
	 * 保存一条数据到大评委结果表
	 */
	public void saveTblXmjg3(TblXmjg3 xmjg3) {
		this.getHibernateTemplate().save(xmjg3);

	}

	/**
	 * 根据项目id查找大评委结果表中的一条记录
	 */
	public TblXmjg3 findTblXmjg3ByBasicInfoId(String basicInfoId,
			String hqlWhere) {
		String hql = "from TblXmjg3 xmjg3 where xmjg3.basicinfo.zlid=?";
		if (!"".equals(hqlWhere) && hqlWhere.length() > 0) {
			hql = hql + " and " + hqlWhere;
		}
		return (TblXmjg3) this.getSession().createQuery(hql).setParameter(0,
				basicInfoId).uniqueResult();
	}

	/**
	 * 更新大评委结果表
	 */
	public void updateTblXmjg3(TblXmjg3 xmjg3) {
		this.getHibernateTemplate().update(xmjg3);

	}

	/**
	 * 查询列出大评委结果表中的所有数据
	 */
	@SuppressWarnings("unchecked")
	public List<TblXmjg3> listAllTblXmjg3(String hqlWhere) {
		String hql = "from TblXmjg3 xmjg3";
		if (!"".equals(hqlWhere) && hqlWhere.length() > 0) {
			hql = hql + " where " + hqlWhere;
		}
		hql = hql + " order by xmjg3.pxjj.id,xmjg3.ps desc";
		return (List<TblXmjg3>) this.getHibernateTemplate().find(hql);
	}

	/**
	 * 清空大评委结果表
	 */
	public void delTblXmjg3(String hqlWhere) {
		String hql = "delete from TblXmjg3 xmjg3";
		if (!"".equals(hqlWhere) && hqlWhere.length() > 0) {
			hql = hql + " where " + hqlWhere;
		}
		this.getSession().createQuery(hql).executeUpdate();

	}

	@SuppressWarnings("unchecked")
	public List<Integer> listXmfs3Expert(String hqlWhere) {
		String hql = "select xmfs3.tblZhuanjiarukuxinxibiao.FBh from TblXmfs3 xmfs3";
		if (!"".equals(hqlWhere) && hqlWhere.length() > 0) {
			hql = hql + " where " + hqlWhere;
		}
		return (List<Integer>)this.getHibernateTemplate().find(hql);
		
	}
}
